package com.eling.elcms.travel.dao.hibernate;

import com.eling.elcms.basedoc.dao.IRoomDao;
import com.eling.elcms.basedoc.dao.IRoomTypeDao;
import com.eling.elcms.basedoc.model.Room;
import com.eling.elcms.basedoc.model.RoomType;
import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import com.eling.elcms.travel.dao.IChangeRoomDao;
import com.eling.elcms.travel.model.ChangeRoom;
import com.eling.elcms.travel.model.NonMemberGroup.Status;
import com.eling.elcms.travel.model.PlanItemGroup.CheckInStatus;
import com.eling.elcms.travel.model.view.ChangeRoomView;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@Repository
public class ChangeRoomDaoHibernate extends
		GenericDaoHibernate<ChangeRoom, Long> implements IChangeRoomDao {
	
	@Autowired
	IRoomDao roomDao;
	
	@Autowired
	IRoomTypeDao roomTypeDao;

	public ChangeRoomDaoHibernate() {
		super(ChangeRoom.class);
	}

	/**
	 * 换房调整查询
	 * @param cond
	 * @return
	 * @author liyan
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<ChangeRoomView> queryGroup(ChangeRoomView cond) {
		List<ChangeRoomView> returnList = new ArrayList<ChangeRoomView>();
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String startDate = df.format(cond.getStartDate());
		String endDate = df.format(cond.getEndDate());
		Long pkServicePoint = null;
		if(cond.getServicePoint()!=null){
			pkServicePoint = cond.getServicePoint().getPkServicePoint();
		}
		String desName = null;
		if(cond.getDestination()!=null){
			desName = cond.getDestination().getName();
		}
		String contactName = cond.getContactName().trim();
		
		ChangeRoomView crv = new ChangeRoomView();
		/**********散客团***********/
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT  ");
		sb.append("td.`name` '目的地',");
		sb.append("tn1.checkInDate '开始日期',  ");
		sb.append("tn1.checkOutDate '结束日期',  ");
		sb.append("tn1.tourType '组团类型1',  ");//3
		sb.append("tn1.groupUnit '组团单位',  ");
		sb.append("tn1.contactName '联系人',  ");
		sb.append("tn1.signUpType  '组团类型2', ");//6
		sb.append("os.name '服务点名', ");
		sb.append("tc.pkCIImplement ");//8
		sb.append("FROM  ");
		sb.append("	travel_checkinimplement tc ");
		sb.append("LEFT JOIN travel_nonmemgroupreserve tn ON tn.pkNonMemGroupReserve = tc.pkNonMemGroupReserve ");
		sb.append("LEFT JOIN travel_nonmembergroup tn1 ON tn1.pkNonMemberGroup = tn.pkNonMemberGroup ");
		sb.append("LEFT JOIN om_servicepoint os ON os.pkServicePoint = tn1.pkServicePoint ");
		sb.append("LEFT JOIN cm_organization co ON co.pkOrganization = os.pkOrganization ");
		sb.append("LEFT JOIN travel_destination td ON td.pkOrganization = co.pkOrganization ");
		sb.append("LEFT JOIN travel_roompersonalInfo tr ON tc.pkCIImplement = tr.pkCIImplement ");
		sb.append("WHERE tc.`status`='Confirmed' ");
		sb.append("AND tn1.status ='"+ Status.CheckIn+"'  ");
		sb.append("AND (tn1.isAbolished = FALSE OR tn1.isAbolished IS NULL)");
		sb.append("AND tr.checkIn='1' ");
		if(contactName!=null&&!contactName.equals("")){
			sb.append("AND tn1.contactName LIKE '%"+contactName+"%' ");
		}
		if(pkServicePoint!=null){
			sb.append("AND os.pkServicePoint = '"+pkServicePoint+"' ");
		}
		if(desName!=null&&!desName.equals("")){
			sb.append("AND td.`name` = '"+desName+"' ");
		}
		sb.append("AND tn1.checkInDate BETWEEN '"+startDate+"' AND '"+endDate+"' ");
		sb.append("GROUP BY tc.pkCIImplement    ");
		String sql = sb.toString();
		List<Object[]> list = getSession().createNativeQuery(sql).list();
		for(Object[] o : list){
			crv = new ChangeRoomView();
			if(o[0]!=null){
				crv.setDname(o[0].toString());
			}
			if(o[1]!=null){
				try {
					crv.setStartDate(df.parse(o[1].toString()));
				} catch (ParseException e) {
					e.printStackTrace();
				}
			} 
			if(o[2]!=null){
				try {
					crv.setEndDate(df.parse(o[2].toString()));
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			if(o[3]!=null){
				if(o[6]!=null){
					if(o[6].toString().equals("NonMember")){
						if("TravelGroup".equals(o[3].toString())){
							crv.setGroupType("旅行团");
						}else if("Individual".equals(o[3].toString())){
							crv.setGroupType("旅行个人");
						}else if("IndivMember".equals(o[3].toString())){
							crv.setGroupType("会员散客");
						}
					}else{
						crv.setGroupType("体验客户");
					}
				}
			}else{
				continue;
			}
			if(o[4]!=null){
				if(crv.getGroupType().equals("体验客户")){
					crv.setGroupUnit(o[7].toString());
				}else if(crv.getGroupType().equals("旅行个人")){
					crv.setGroupUnit("个人-" + o[5].toString());
				}else if(crv.getGroupType().equals("会员散客")){
					crv.setGroupUnit("个人-" + o[5].toString());
				}else {
					crv.setGroupUnit((o[4].toString()));
				}
			}
			if(o[5]!=null){
				crv.setContactName(o[5].toString());
			}
			if(o[8]!=null){
				crv.setPkCIImplement(o[8].toString());
			}else{
				continue;
			}
			returnList.add(crv);
		}
	
		/**********团体***********/
		sb = new StringBuffer();
		sb.append("SELECT  ");
		sb.append("td.`name` '目的地', ");
		sb.append("tp1.startDate '开始日期', ");
		sb.append("tp1.endDate '结束日期', ");
		sb.append("tp1.alliance '是否是联盟', "); //组团类型
		sb.append("ta.name '组团单位1', ");
		sb.append("tp1.pkDestination, "); // 无用，本来是联系人  但是团没有联系人。
		sb.append("os.`name` '组团单位2', ");
		sb.append("tg.checkInType '入住类型', ");
		sb.append("tc.pkCIImplement ");//8
		sb.append("FROM  ");
		sb.append("	travel_checkinimplement tc ");
		sb.append("LEFT JOIN travel_groupreserve tg ON tg.pkGroupReserve = tc.pkGroupReserve ");
		sb.append("LEFT JOIN travel_planitemgroup tp ON tp.pkPlanItemGroup = tg.pkPlanItemGroup ");
		sb.append("LEFT JOIN travel_planitem tp1 ON tp.pkPlanItem = tp1.pkPlanItem ");
		sb.append("LEFT JOIN travel_destination td ON td.pkDestination = tp1.pkDestination ");
		sb.append("LEFT JOIN travel_allianceorg ta ON ta.pkAllianceOrg = tp1.pkAllianceOrg ");
		sb.append("LEFT JOIN om_servicepoint os ON os.pkServicePoint = tp.pkServicePoint  ");
		sb.append("LEFT JOIN travel_signupregister ts ON ts.pkPlanItemGroup = tp.pkPlanItemGroup  ");
		sb.append("LEFT JOIN bd_personalinfo bp ON ts.pkPersonalInfo = bp.pkPersonalInfo  ");
		sb.append("LEFT JOIN travel_checkin tc1 ON tc.pkCIImplement = tc1.pkCheckInImplement ");
		sb.append("WHERE tc.`status`='Confirmed' ");
		sb.append("AND tp.checkInStatus='"+ CheckInStatus.CheckIn+"'  ");
		sb.append("AND (tp.isAbolished = FALSE OR tp.isAbolished IS NULL)");
		sb.append("AND tc1.registerDate!=''  ");
		if(contactName!=null&&!contactName.equals("")){
			sb.append("AND bp.`name` LIKE '%"+contactName+"%' ");
		}
		if(pkServicePoint!=null){
			sb.append("AND os.pkServicePoint = '"+pkServicePoint+"' ");
		}
		if(desName!=null&&!desName.equals("")){
			sb.append("AND td.`name` = '"+desName+"' ");
		}
		sb.append("AND tc1.checkInDate BETWEEN '"+startDate+"' AND '"+endDate+"' ");
		sb.append("GROUP BY tc.pkCIImplement   ");
		sql = sb.toString();
		list = getSession().createNativeQuery(sql).list();
		for(Object[] o : list){
			crv = new ChangeRoomView();
			if(o[0]!=null){
				crv.setDname(o[0].toString());
			}
			if(o[1]!=null){
				try {
					crv.setStartDate(df.parse(o[1].toString()));
				} catch (ParseException e) {
					e.printStackTrace();
				}
			} 
			if(o[2]!=null){
				try {
					crv.setEndDate(df.parse(o[2].toString()));
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			if(o[3]!=null){
				if(o[3].toString().equals("true")){
					if(o[7]!=null){
						if(o[7].toString().equals("Organization")){
							crv.setGroupType("联盟入住直属");
						}else{
							crv.setGroupType("联盟入住联盟");
						}
					}
				}else{
					if(o[7]!=null){
						if(o[7].toString().equals("Organization")){
							crv.setGroupType("直属入住直属");
						}else{
							crv.setGroupType("直属入住联盟");
						}
					}
				}
			}else{
				if(o[7]!=null){
					if(o[7].toString().equals("Organization")){
						crv.setGroupType("直属入住直属");
					}else{
						crv.setGroupType("直属入住联盟");
					}
				}
			}
			if(o[4]!=null){
				crv.setGroupUnit((o[4].toString()));
			}else if(o[6]!=null){
				crv.setGroupUnit((o[6].toString()));
			}
			if(o[5]!=null){
				crv.setContactName("团体无联系人");
			}
			if(o[8]!=null){
				crv.setPkCIImplement(o[8].toString());
			}else{
				continue;
			}
			returnList.add(crv);
		}
		//对returnList按开始日期进行排序
		Comparator<ChangeRoomView> comparator = new Comparator<ChangeRoomView>() {
			@Override
			public int compare(ChangeRoomView o1, ChangeRoomView o2) {
				if(o1.getStartDate()==null || o2.getStartDate()==null){
					return -1;
				}
				if(o1.getStartDate().compareTo(o2.getStartDate())<0){
					return 1;
				}else if(o1.getStartDate().compareTo(o2.getStartDate())>0){
					return -1;
				}else{
					return 0;
				}
			}
		};
		Collections.sort(returnList,comparator);
		for(int j=0;j<returnList.size();j++){
			ChangeRoomView c = returnList.get(j);
			Date date = new Date();
			List<Date> listDate = new ArrayList<Date>();
			List<ChangeRoomView> queryGroupDetail = queryGroupDetail(c);
			for(ChangeRoomView cc : queryGroupDetail){
				Date changeRoomDate = cc.getChangeRoomDate();
				if(changeRoomDate!=null){
					listDate.add(changeRoomDate);
				}
			}
			if(listDate.size()>0){
				if(listDate.size()>1){
					for(int i=0;i<listDate.size()-1;i++){
						if(listDate.get(i).compareTo(listDate.get(i+1))<0){
							date = listDate.get(i).compareTo(listDate.get(i+1))>0?listDate.get(i) : listDate.get(i+1);
						}
					}
				}else{
					date = listDate.get(0);
				}
				c.setChangeRoomDate(date);
				returnList.remove(j);
				returnList.add(j,c);
			}
		}
		return returnList;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<ChangeRoomView> queryGroupDetail(ChangeRoomView cond) {
		if(cond.getGroupType()==null){
			return  null;
		}
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		List<ChangeRoomView> returnList = new ArrayList<ChangeRoomView>();
		ChangeRoomView crdv = new ChangeRoomView();
		StringBuffer sb = new StringBuffer();
		String sql = null;
		List<Object[]> list = null;
		
		/**散客团**/
		if(!cond.getGroupType().contains("直属") && !cond.getGroupType().contains("联盟") ){
			sb.append(" SELECT  ");
			sb.append("	bp.idNumber '身份证号',");
			sb.append("	bp.`name` '姓名',");
			sb.append("	bp.sex '性别',");
			sb.append("	tr2.`name` '房型',");
			sb.append("	br.`code` '房号' ,");
			sb.append(" tn1.contactName '联系人',");
			sb.append(" tn1.groupUnit '组团单位',");
			sb.append("	br1.`code` '调整后的房号',");//7
			sb.append("	tr3.`name` '调整后的房型',");//8
			sb.append("	tc1.changeRoomDate '调整日期',");
			sb.append("	bp.pkPersonalInfo,");
			sb.append("	tc1.pkChangeRoom, ");
			sb.append("	br1.pkRoom, ");//12
			sb.append("	tr3.pkRoomType, ");//13
			sb.append("	tr1.waterElectric ");//14
			sb.append(" FROM  ");
			//身份证 姓名  性别
			sb.append("	travel_checkinimplement tc ");
			sb.append("LEFT JOIN travel_nonmemgroupreserve tn ON tn.pkNonMemGroupReserve = tc.pkNonMemGroupReserve ");
			sb.append("LEFT JOIN travel_nonmembergroup tn1 ON tn.pkNonMemberGroup = tn1.pkNonMemberGroup ");
			sb.append("LEFT JOIN travel_nonmemberregister tn2 ON tn2.pkNonMemberGroup = tn1.pkNonMemberGroup ");
//			sb.append("LEFT JOIN travel_signupregister ts ON ts.pkSignUpRegister = tn2.pkSignUpRegister ");
//			sb.append("LEFT JOIN bd_personalinfo bp ON bp.pkPersonalInfo = ts.pkPersonalInfo ");
			//房型 房号
			sb.append("LEFT JOIN travel_roompersonalInfo tr ON tc.pkCIImplement = tr.pkCIImplement ");
			sb.append("LEFT JOIN travel_roomregister tr1 ON tr1.pkRoomRegister = tr.pkRoomRegister ");
			sb.append("LEFT JOIN bd_room br ON br.pkRoom = tr1.pkRoom ");
			sb.append("LEFT JOIN travel_roomtype tr2 ON tr2.pkRoomType = br.pkRoomType ");
			
			sb.append("JOIN travel_nonmemberregister tnmr ON tnmr.pkNonMemberRegister=tr.pkNonMemberRegister  ");
			sb.append("	JOIN travel_signupregister ts ON ts.pkSignUpRegister = tnmr.pkSignUpRegister  ");
			sb.append("	JOIN bd_personalinfo bp ON bp.pkPersonalInfo = ts.pkPersonalInfo  ");

			//目的地
			sb.append("LEFT JOIN om_servicepoint os ON os.pkServicePoint = tn1.pkServicePoint ");
			sb.append("LEFT JOIN cm_organization co ON co.pkOrganization = os.pkOrganization ");
			sb.append("LEFT JOIN travel_destination td ON td.pkOrganization = co.pkOrganization ");
			//调整后的房型 房号
			sb.append("LEFT JOIN travel_changeroom tc1 ON tc1.pkPersonalInfo = bp.pkPersonalInfo ");
			sb.append("LEFT JOIN bd_room br1 ON br1.pkRoom = tc1.pkRoom ");
			sb.append("LEFT JOIN travel_roomtype tr3 ON tr3.pkRoomType = br1.pkRoomType ");
			
			sb.append("WHERE tc.pkCIImplement='"+cond.getPkCIImplement()+"'  ");
			sb.append("AND (ts.exitGroup = FALSE ");
			sb.append(" OR ts.exitGroup IS NULL) ");
			sb.append("AND (ts.checkOut = FALSE ");
			sb.append(" OR ts.checkOut IS NULL) ");
			sb.append("GROUP BY bp.idNumber,tc1.pkChangeRoom  ");
			sql = sb.toString();		
			list = getSession().createNativeQuery(sql).list();
			for(Object[] o : list){
				crdv = new ChangeRoomView();
				if(o[0]!=null){
					crdv.setIdNumber(o[0].toString());
				}
				if(o[1]!=null){
					crdv.setName(o[1].toString());
				}
				if(o[2]!=null){
					if(o[2].toString().equalsIgnoreCase("Male")){
						crdv.setSex("男");
					}else if(o[2].toString().equalsIgnoreCase("Female")){
						crdv.setSex("女");
					}
				}
				if(o[3]!=null){
					crdv.setRoomType(o[3].toString());
				}
				if(o[4]!=null){
					crdv.setRoomCode(o[4].toString());
				}
				if(o[5]!=null){
					crdv.setContactName(o[5].toString());
				}
				if(o[6]!=null){
					crdv.setGroupUnit(o[6].toString());
				}
				if(o[7]!=null){
					crdv.setChangeRoomCode(o[7].toString());
					if(o[12]!=null){
						Room croom = roomDao.get(Long.parseLong(o[12].toString()));
						croom.setCode(o[7].toString());
						crdv.setCroom(croom);
					}
				}
				if(o[8]!=null){
					crdv.setChangeRoomType(o[8].toString());
					if(o[13]!=null){
						RoomType croomType = roomTypeDao.get(Long.parseLong(o[13].toString()));
						croomType.setName(o[8].toString());
						crdv.setCroomType(croomType);
					}
				}
				if(o[9]!=null){
					try {
						crdv.setChangeRoomDate(df.parse(o[9].toString()));
					} catch (ParseException e) {
						e.printStackTrace();
					}
				}
				//pkPersonalInfo不能为空，否则就跳过,因为如果pkPersonalInfo为空，联表查询出的pkChangeRoom也为空，就会一直新增数据，垃圾数据
				if(o[10]!=null){
					crdv.setPkPersonalInfo(o[10].toString());
				}else{
					continue;
				}
				
				if(o[11]!=null){
					crdv.setPkChangeRoom(o[11].toString());
				}
				if(o[14]!=null){
					crdv.setWaterElectric(Boolean.valueOf(o[14].toString()));
				}
				returnList.add(crdv);
			}
		} else {
			/**团体**/
			sb = new StringBuffer();
			sb.append(" SELECT  ");
			sb.append("bp.idNumber '身份证号',");
			sb.append("bp.`name` '姓名',");
			sb.append("bp.sex '性别',");
			sb.append("tr3.`name` '房型',");
			sb.append("br.`code` '房号',");
			sb.append("bp.`name` '联系人',");//联系人还有问题不能这样
			sb.append("ta.name '组团单位1', ");//有1用1 没1用2
			sb.append("bd.`code` '调整后的房号',");
			sb.append("tr.`name` '调整后的房型',");
			sb.append("tc1.changeRoomDate '调整日期',");
			sb.append("bp.pkPersonalInfo,");
			sb.append("tc1.pkChangeRoom, ");
			sb.append("os.`name` '组团单位2',  ");
			sb.append("	bd.pkRoom, ");//13
			sb.append("	tr.pkRoomType, ");//14
			sb.append("	tr1.waterElectric ");//15
			sb.append(" FROM  ");
			sb.append("	travel_checkinimplement tc ");
			sb.append("LEFT JOIN travel_groupreserve tg ON tg.pkGroupReserve = tc.pkGroupReserve ");
			sb.append("LEFT JOIN travel_planitemgroup tp ON tp.pkPlanItemGroup = tg.pkPlanItemGroup ");
			sb.append("LEFT JOIN travel_planitem tp1 ON tp.pkPlanItem = tp1.pkPlanItem ");
			
			sb.append("LEFT JOIN travel_destination td ON td.pkDestination = tp1.pkDestination ");
			sb.append("LEFT JOIN travel_allianceorg ta ON ta.pkAllianceOrg = tp1.pkAllianceOrg    ");
			sb.append("LEFT JOIN om_servicepoint os ON os.pkServicePoint = tp.pkServicePoint   ");
			/*房型 房号*/
			sb.append("LEFT JOIN travel_roompersonalInfo tr2 ON tc.pkCIImplement = tr2.pkCIImplement   ");
			sb.append("LEFT JOIN travel_roomregister tr1 ON tr1.pkRoomRegister = tr2.pkRoomRegister   ");
			sb.append("LEFT JOIN bd_room br ON br.pkRoom = tr1.pkRoom   ");
			sb.append("LEFT JOIN travel_roomtype tr3 ON tr3.pkRoomType = br.pkRoomType   ");
			
			sb.append("JOIN travel_signupregister ts ON ts.pkSignUpRegister = tr2.pkSignUpRegister   ");
			sb.append("JOIN bd_personalinfo bp ON ts.pkPersonalInfo = bp.pkPersonalInfo    ");
			 /*调整后的房型 房号*/
			sb.append("LEFT JOIN travel_changeroom tc1 ON bp.pkPersonalInfo = tc1.pkPersonalInfo   ");
			sb.append("LEFT JOIN bd_room bd ON tc1.pkRoom = bd.pkRoom   ");
			sb.append("LEFT JOIN travel_roomtype tr ON bd.pkRoomType = tr.pkRoomType   ");
			
			sb.append("WHERE tc.pkCIImplement='" +cond.getPkCIImplement()+"' ");
			sb.append("AND (ts.exitGroup = FALSE ");
			sb.append(" OR ts.exitGroup IS NULL) ");
			sb.append("AND (ts.checkOut = FALSE ");
			sb.append(" OR ts.checkOut IS NULL) ");
			sql = sb.toString();		
			list = getSession().createNativeQuery(sql).list();
			for(Object[] o : list){
				crdv = new ChangeRoomView();
				if(o[0]!=null){
					crdv.setIdNumber(o[0].toString());
				}
				if(o[1]!=null){
					crdv.setName(o[1].toString());
				}
				if(o[2]!=null){
					if(o[2].toString().equalsIgnoreCase("Male")){
						crdv.setSex("男");
					}else if(o[2].toString().equalsIgnoreCase("Female")){
						crdv.setSex("女");
					}
				}
				if(o[3]!=null){
					crdv.setRoomType(o[3].toString());
				}
				if(o[4]!=null){
					crdv.setRoomCode(o[4].toString());
				}
				if(o[5]!=null){
					crdv.setContactName(o[5].toString());
				}
				if(o[6]!=null){
					crdv.setGroupUnit(o[6].toString());
				}else if(o[12]!=null){
					crdv.setGroupUnit(o[12].toString());
				}
				if(o[7]!=null){
					crdv.setChangeRoomCode(o[7].toString());
					if(o[13]!=null){
						Room croom = roomDao.get(Long.parseLong(o[13].toString()));
						croom.setCode(o[7].toString());
						crdv.setCroom(croom);
					}
				}
				if(o[8]!=null){
					crdv.setChangeRoomType(o[8].toString());
					if(o[14]!=null){
						RoomType croomType = roomTypeDao.get(Long.parseLong(o[14].toString()));
						croomType.setName(o[8].toString());
						crdv.setCroomType(croomType);
					}
				}
				if(o[9]!=null){
					try {
						crdv.setChangeRoomDate(df.parse(o[9].toString()));
					} catch (ParseException e) {
						e.printStackTrace();
					}
				}
				//pkPersonalInfo不能为空，否则就跳过,因为如果pkPersonalInfo为空，联表查询出的pkChangeRoom也为空，就会一直新增数据，垃圾数据
				if(o[10]!=null){
					crdv.setPkPersonalInfo(o[10].toString());
				}else{
					continue;
				}
				if(o[11]!=null){
					crdv.setPkChangeRoom(o[11].toString());
				}
				if(o[15]!=null){
					crdv.setWaterElectric(Boolean.valueOf(o[15].toString()));
				}
				returnList.add(crdv);
			}
		}
		
//		把returnList去重  只留时间最新的。
		for(int i = 0;i<returnList.size()-1;i++){
			if(returnList.get(i+1).getIdNumber().equals(returnList.get(i).getIdNumber())){
				returnList.remove(i);
				i -= 1;
			}
		}
		return returnList;
	}

	@Override
	public Room queryRoomByRoomCode(String cond) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT  ");
		sb.append("	br.pkRoom ");
		sb.append("FROM  ");
		sb.append("	bd_room br  ");
		sb.append("LEFT JOIN travel_roomtype tr ON br.pkRoomType = tr.pkRoomType  "); 
		sb.append("LEFT JOIN bd_building bb ON br.pkOrgBuilding = bb.pkOrgBuilding ");
		sb.append("LEFT JOIN om_servicepoint os ON bb.pkServicePoint = os.pkServicePoint ");
		sb.append("WHERE ");
		sb.append("	br.pkRoomType != '' ");
		sb.append("AND br.`code` = '" + cond + "'  ");
		sb.append("GROUP BY br.pkRoom ");
//		sb.append("AND tr.`name` = '"+cond.getCroomType().getName()+"' ");
		Object uniqueResult = getSession().createNativeQuery(sb.toString()).uniqueResult();
		if(uniqueResult==null){
			return null;
		}
		String str = uniqueResult.toString();
		long l = Long.parseLong(str);
		Room r = roomDao.get(l);
		return r;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ChangeRoomView> searchname(ChangeRoomView cond) {
		List<ChangeRoomView> returnList = new ArrayList<ChangeRoomView>();
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String startDate = df.format(cond.getStartDate());
		String endDate = df.format(cond.getEndDate());
		Long pkServicePoint = null;
		if(cond.getServicePoint()!=null){
			pkServicePoint = cond.getServicePoint().getPkServicePoint();
		}
		String desName = null;
		if(cond.getDestination()!=null){
			desName = cond.getDestination().getName();
		}
		String contactName = cond.getContactName().trim();
		ChangeRoomView crv = new ChangeRoomView();
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT  ");
		sb.append("td.`name` '目的地',");
		sb.append("tn1.checkInDate '开始日期',  ");
		sb.append("tn1.checkOutDate '结束日期',  ");
		sb.append("tn1.tourType '组团类型1',  ");//3
		sb.append("tn1.groupUnit '组团单位',  ");
		sb.append("tn1.contactName '联系人',  ");
		sb.append("tn1.signUpType  '组团类型2', ");//6
		sb.append("os.name '服务点名', ");
		sb.append("tc.pkCIImplement ");//8
		sb.append("FROM  ");
		sb.append("	travel_checkinimplement tc ");
		sb.append("LEFT JOIN travel_nonmemgroupreserve tn ON tn.pkNonMemGroupReserve = tc.pkNonMemGroupReserve ");
		sb.append("LEFT JOIN travel_nonmembergroup tn1 ON tn1.pkNonMemberGroup = tn.pkNonMemberGroup ");
		sb.append("LEFT JOIN om_servicepoint os ON os.pkServicePoint = tn1.pkServicePoint ");
		sb.append("LEFT JOIN cm_organization co ON co.pkOrganization = os.pkOrganization ");
		sb.append("LEFT JOIN travel_destination td ON td.pkOrganization = co.pkOrganization ");
		sb.append("WHERE tc.`status`='Confirmed' ");
		if(contactName!=null&&!contactName.equals("")){
			sb.append("AND tn1.contactName LIKE '%"+contactName+"%' ");
		}
		if(pkServicePoint!=null){
			sb.append("AND os.pkServicePoint = '"+pkServicePoint+"' ");
		}
		if(desName!=null&&!desName.equals("")){
			sb.append("AND td.`name` = '"+desName+"' ");
		}
		sb.append("AND tn1.checkInDate BETWEEN '"+startDate+"' AND '"+endDate+"' ");
		sb.append("GROUP BY tc.pkCIImplement    ");
		sb.append("ORDER BY tn1.checkInDate DESC   ");
		String sql = sb.toString();
		List<Object[]> list = getSession().createNativeQuery(sql).list();
		for(Object[] o : list){
			crv = new ChangeRoomView();
			if(o[0]!=null){
				crv.setDname(o[0].toString());
			}
			if(o[1]!=null){
				try {
					crv.setStartDate(df.parse(o[1].toString()));
				} catch (ParseException e) {
					e.printStackTrace();
				}
			} 
			if(o[2]!=null){
				try {
					crv.setEndDate(df.parse(o[2].toString()));
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			if(o[3]!=null){
				if(o[6]!=null){
					if(o[6].toString().equals("NonMember")){
						if("TravelGroup".equals(o[3].toString())){
							crv.setGroupType("旅行团");
						}else if("Individual".equals(o[3].toString())){
							crv.setGroupType("旅行个人");
						}else if("IndivMember".equals(o[3].toString())){
							crv.setGroupType("会员散客");
						}
					}else{
						crv.setGroupType("体验客户");
					}
				}
			}else{
				continue;
			}
			if(o[4]!=null){
				if(crv.getGroupType().equals("体验客户")){
					crv.setGroupUnit(o[7].toString());
				}else if(crv.getGroupType().equals("旅行个人")){
					crv.setGroupUnit("个人-" + o[5].toString());
				}else if(crv.getGroupType().equals("会员散客")){
					crv.setGroupUnit("个人-" + o[5].toString());
				}
				crv.setGroupUnit((o[4].toString()));
			}
			if(o[5]!=null){
				crv.setContactName(o[5].toString());
			}else{
				continue;
			}
			if(o[8]!=null){
				crv.setPkCIImplement(o[8].toString());
			}else{
				continue;
			}
			returnList.add(crv);
		}
		
		for(int j=0;j<returnList.size();j++){
			ChangeRoomView c = returnList.get(j);
			Date date = new Date();
			List<Date> listDate = new ArrayList<Date>();
			List<ChangeRoomView> queryGroupDetail = queryGroupDetail(c);
			for(ChangeRoomView cc : queryGroupDetail){
				Date changeRoomDate = cc.getChangeRoomDate();
				if(changeRoomDate!=null){
					listDate.add(changeRoomDate);
				}
			}
			if(listDate.size()>0){
				if(listDate.size()>1){
					for(int i=0;i<listDate.size()-1;i++){
						if(listDate.get(i).compareTo(listDate.get(i+1))<0){
							date = listDate.get(i).compareTo(listDate.get(i+1))>0?listDate.get(i) : listDate.get(i+1);
						}
					}
				}else{
					date = listDate.get(0);
				}
				c.setChangeRoomDate(date);
				returnList.remove(j);
				returnList.add(j,c);
			}
		}
		
		return returnList;
	}
	
	
}
